﻿using System.Data;
using System.Data.SQLite;

namespace DBScript
{
    /// <summary>
    /// SQLiteHelper 的摘要说明
    /// 此类未经实践，可能会进行大幅度重构
    /// </summary>
    public class SQLiteHelper
    {

        //public string SQLiteConnectionString = ConfigurationManager.ConnectionStrings["SQLiteConnectString"].ConnectionString;
        //轻量级数据库SQLite的连接字符串写法："Data Source=D:\database\test.s3db"
        //轻量级数据库SQLite的加密后的连接字符串写法："Data Source=D:"database\test.s3db;Version=3;Password=你的SQLite数据库密码;"

        private string _SQLiteConnectionString;
        public string SQLiteConnectionString
        {
            get
            {
                return _SQLiteConnectionString;
            }
            set
            {
                _SQLiteConnectionString = value;
            }
        }
        public SQLiteHelper(string conn)
        {
            SQLiteConnectionString = conn;
        }

        #region ExecuteNonQuery

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = new SQLiteConnection(SQLiteConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                con.Open();
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteNonQuery(commandText, false, paras);
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {

            SQLiteConnection con = trans.Connection;
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteNonQuery(trans, commandText, false, paras);
        }

        #endregion

        #region ExecuteQueryScalar

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = new SQLiteConnection(SQLiteConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                con.Open();
                return cmd.ExecuteScalar();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteQueryScalar(commandText, false, paras);
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = trans.Connection;
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }
            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteScalar();
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteQueryScalar(trans, commandText, false, paras);
        }

        #endregion

        #region ExecuteDataReader

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public SQLiteDataReader ExecuteDataReader(string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = new SQLiteConnection(SQLiteConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                con.Close();
                throw;
            }

        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public SQLiteDataReader ExecuteDataReader(string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteDataReader(commandText, false, paras);
        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public SQLiteDataReader ExecuteDataReader(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = trans.Connection;
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                if (trans == null)
                {
                    con.Close();
                }
                throw;
            }

        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public SQLiteDataReader ExecuteDataReader(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteDataReader(trans, commandText, false, paras);
        }

        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = new SQLiteConnection(SQLiteConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                con.Open();
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteDataSet(commandText, false, paras);
        }

        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = trans.Connection;
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteDataSet(trans, commandText, false, paras);
        }

        #endregion
    }
}
